package org.iteam.xlz.core.service.ad;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.iteam.core.utils.SqlBuilder;
import org.iteam.db.BaseService;
import org.iteam.xlz.core.entity.ad.AdStrategyVo;

public class AdStrategyService extends BaseService<AdStrategyVo>{
	static AdStrategyService instance;
	private List<Object> params=new ArrayList<Object>();
	
	private AdStrategyService(){
	}
	
	public static AdStrategyService getInstance(){
		if(null==instance){
			instance=new AdStrategyService();
		}
		return instance;
	}
	
	public long counts(){
		try {
			return super.counts("select count(1) from adstrategy where 1=1");
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public List<AdStrategyVo> query(long page,long rows){
		params.clear();
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select a.id id,posName adPosName,GROUP_CONCAT(title) adName,devType,ver,startTime,endTime,devId,a.createTime,a.creator from adstrategy a");
			sql.append(" left join adid b on a.id = b.sid");
			sql.append(" left join adinfo c on b.aid = c.id");
			sql.append(" left join adpos d on a.adPosId = d.id");
			sql.append(" where 1=1 group by a.id limit ?,?");
			params.add((page-1)*rows);
			params.add(rows);
			List<AdStrategyVo> adList = super.query(sql.toString(),params.toArray());
			return adList;
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<AdStrategyVo>();
		}
	}
	
	
	public boolean create(AdStrategyVo model){
		Connection con=null;
		String sid=UUID.randomUUID().toString().replace("-","").toUpperCase();
		model.setCreateTime(new Date());
		model.setId(sid);
		try {
			con=super.getConnection(false);
			SqlBuilder builder=SqlBuilder.createInsert("adstrategy");
			builder.addField("id",model.getId());
			builder.addField("adPosId",model.getAdPosId());
			builder.addField("devType",model.getDevType());
			builder.addField("ver",model.getVer());
			builder.addField("startTime",model.getStartTime());
			builder.addField("endTime",model.getEndTime());
			if(StringUtils.isNotBlank(model.getDevId())){
				model.setDevId(model.getDevId().replaceAll(" ", ""));
			}
			builder.addField("devId",model.getDevId());
			builder.addField("createTime",model.getCreateTime());
			builder.addField("creator",model.getCreator());
			int j=super.updates(con,builder.getSql(),builder.getParams());
			if(StringUtils.isNotBlank(model.getAdId())){
				String[] adids = model.getAdId().replaceAll(" ", "").split(",");
				int i=0;
				for (String aid : adids) {
					SqlBuilder adidBuilder = SqlBuilder.createInsert("adid");
					adidBuilder.addField("aid", aid);
					adidBuilder.addField("sid", model.getId());
					i +=super.updates(con,adidBuilder.getSql(),adidBuilder.getParams());
				}
				if(j == 1 && i == adids.length){
					con.commit();
					return true;
				}else{
					con.rollback();
					return false;
				}
			}
			if(j==1){
				con.commit();
				return true;
			}else{
				con.rollback();
				return false;
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
			try {
				super.rollback(con);
				return false;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
	}
	
	public boolean update(AdStrategyVo model){
		Connection con=null;
		try {
			con=super.getConnection(false);
			SqlBuilder builder=SqlBuilder.createUpdate("adstrategy");
			if(StringUtils.isNotBlank(model.getDevId())){
				model.setDevId(model.getDevId().replaceAll(" ", ""));
			}
			builder.addField("devId",model.getDevId());
			builder.addField("devType",model.getDevType());
			builder.addField("ver",model.getVer());
			builder.addField("startTime",model.getStartTime());
			builder.addField("endTime",model.getEndTime());
			builder.addWhere("id",model.getId());
			int j=super.updates(con,builder.getSql(),builder.getParams());
			if(StringUtils.isNotBlank(model.getAdId())){
				String[] adids = model.getAdId().replaceAll(" ", "").split(",");
				SqlBuilder adidBuilder = SqlBuilder.createDelete("adid");
				adidBuilder.addWhere("sid", model.getId());
				int i =super.updates(con,adidBuilder.getSql(),adidBuilder.getParams());
				if(i>0){
					int w = 0;
					for (String aid : adids) {
						SqlBuilder adidInsert = SqlBuilder.createInsert("adid");
						adidInsert.addField("aid", aid);
						adidInsert.addField("sid", model.getId());
						w +=super.updates(con,adidInsert.getSql(),adidInsert.getParams());
					}
					if(j == 1 && w == adids.length){
						con.commit();
						return true;
					}else{
						con.rollback();
						return false;
					}
				}else{
					con.rollback();
					return false;
				}
			}
			if(j==1){
				con.commit();
				return true;
			}else{
				con.rollback();
				return false;
			}
		} catch (Exception e1) {
			e1.printStackTrace();
			try {
				super.rollback(con);
				return false;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
	}

	
	public AdStrategyVo findById(String id){
		params.clear();
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select a.id id,posName,GROUP_CONCAT(title) adName,GROUP_CONCAT(c.id) adid,d.id adPosId, devType,ver,startTime,endTime,devId,a.createTime,a.creator from adstrategy a");
			sql.append(" left join adid b on a.id = b.sid");
			sql.append(" left join adinfo c on b.aid = c.id");
			sql.append(" left join adpos d on a.adPosId = d.id");
			sql.append(" where 1=1 and a.id=? group by a.id");
			params.add(id);
			return super.show(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
			return new AdStrategyVo();
		}
	}
	
	public boolean delete(String id){
		Connection con=null;
		try {
			con=super.getConnection(false);
			SqlBuilder builder=SqlBuilder.createDelete("adstrategy");
			SqlBuilder adidbuilder=SqlBuilder.createDelete("adid");
			adidbuilder.addWhere("sid", id);
			int i = super.updates(con,adidbuilder.getSql(),adidbuilder.getParams());
			builder.addWhere("id", id);
			int j = super.updates(con,builder.getSql(),builder.getParams());
			if(i>0 && j>0){
				con.commit();
				return true;
			}else{
				con.rollback();
				return false;
			}
		} catch (Exception e1) {
			e1.printStackTrace();
			try {
				super.rollback(con);
				return false;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
	}	
	
	/*public List<AdStrategyVo> getAdStrategyVoByAid(String aid){
		try {
			return super.query("select * from adstrategy where 1=1 and find_in_set(?,adId)",aid);
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<AdStrategyVo>();
		}
	}*/
}
